package com.snake.db;

import com.snake.bean.*;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/12/30 0030.
 */
public class Demo {


/*    public static void main(String[] args) {

        String sql = null;
        DBHelper db1 = null;
        ResultSet ret = null;
        sql = "insert into hf_goods(shop_id,shop_name,title,wholesale_price,taobao_price,product_no,small_picture,big_picture,inventory,taobao_link)values(?,?,?,?,?,?,?,?,?,?)";//SQL语句
        db1 = new DBHelper(sql);//创建DBHelper对象

        List<String> list = new ArrayList();
        try {
            ret = db1.pst.executeQuery();//执行语句，得到结果集

            while (ret.next()) {
                String uid = ret.getString(1);
                list.add(uid);

            }//显示数据
            for (String u : list) {
                db1.pst.execute("delete from qmtk_goods where  qmtk_good_id = " + u);
                db1.conn.setAutoCommit(false);
                db1.conn.commit();
                System.out.println(u + "==》删除完毕");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                ret.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            db1.close();//关闭连接
        }

    }*/

    public void saveHfGoodsData(HfGoods hfGoods) {
        StringBuffer sql = new StringBuffer("insert into hf_goods(shop_id,taobao_good_id,shop_name,title,wholesale_price," +
                "taobao_price,product_no,size,color,small_picture,big_picture,inventory,taobao_link) values(");
        DBHelper db1 = null;
        ResultSet ret = null;
        //SQL语句
        sql.append("'");
        sql.append(hfGoods.getShopId());
        sql.append("',");

        sql.append(hfGoods.getTaoBaoGoodId());
        sql.append(",");
        sql.append("'");
        sql.append(hfGoods.getShopName());
        sql.append("',");
        sql.append("'");
        sql.append(hfGoods.getTitle());
        sql.append("',");

        sql.append(hfGoods.getWholesalePrice());
        sql.append(",");

        sql.append(hfGoods.getTaobaoPrice());
        sql.append(",");

        sql.append(hfGoods.getProductNo());
        sql.append(",");
        sql.append("'");
        sql.append(hfGoods.getSize());
        sql.append("',");
        sql.append("'");
        sql.append(hfGoods.getColor());
        sql.append("',");
        sql.append("'");
        sql.append(hfGoods.getSmallPicture());
        sql.append("',");
        sql.append("'");
        sql.append(hfGoods.getBigPicture());
        sql.append("',");

        sql.append(hfGoods.getInventory());
        sql.append(",");
        sql.append("'");
        sql.append(hfGoods.getTaobaoLink());
        sql.append("'");
        sql.append(")");

        db1 = new DBHelper(sql.toString());//创建DBHelper对象
        try {
         /* *//* db1.pst.setLong(1,hfGoods.getHfUid());
           db1.pst.setString(2,hfGoods.getShopName());*//*
           db1.pst.setString(1,hfGoods.getTitle());
           db1.pst.setInt(2,hfGoods.getWholesalePrice());
           db1.pst.setInt(3,hfGoods.getTaobaoPrice());
  *//*         db1.pst.setLong(6,hfGoods.getProductNo());
           db1.pst.setString(7,hfGoods.getSmallPicture());
           db1.pst.setString(8,hfGoods.getBigPicture());
           db1.pst.setInt(9,hfGoods.getInventory());*//*
           db1.pst.setString(4,hfGoods.getTaobaoLink());*/
            db1.pst.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db1.close();
        }
    }

    public void saveGoodInfoData(HfGoodDescription hfGoodDescription) {
        StringBuffer sql = new StringBuffer("insert into hf_good_description(hf_good_id,taobao_good_id,good_info,attributes) values(");
        DBHelper db1 = null;
        ResultSet ret = null;
        //SQL语句
        sql.append(hfGoodDescription.getHfGoodId());

        sql.append(",");

        sql.append(hfGoodDescription.getTaobaoGoodId());

        sql.append(",");
        sql.append("'");
        sql.append(hfGoodDescription.getGoodInfo());
        sql.append("'");
        sql.append(",");
        sql.append("'");
        sql.append(hfGoodDescription.getAttributes());
        sql.append("'");
        sql.append(")");

        db1 = new DBHelper(sql.toString());//创建DBHelper对象
        try {
         /* *//* db1.pst.setLong(1,hfGoods.getHfUid());
           db1.pst.setString(2,hfGoods.getShopName());*//*
           db1.pst.setString(1,hfGoods.getTitle());
           db1.pst.setInt(2,hfGoods.getWholesalePrice());
           db1.pst.setInt(3,hfGoods.getTaobaoPrice());
  *//*         db1.pst.setLong(6,hfGoods.getProductNo());
           db1.pst.setString(7,hfGoods.getSmallPicture());
           db1.pst.setString(8,hfGoods.getBigPicture());
           db1.pst.setInt(9,hfGoods.getInventory());*//*
           db1.pst.setString(4,hfGoods.getTaobaoLink());*/
            db1.pst.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db1.close();
        }
    }

    public List<HfTaobaoShop> getTaobaoShop() {
        StringBuffer sql = new StringBuffer("select shop_id,  shop_domain from hf_taobao_shop");
        DBHelper db1 = null;
        ResultSet ret = null;
        List<HfTaobaoShop> listLink = new ArrayList<HfTaobaoShop>();
        db1 = new DBHelper(sql.toString());//创建DBHelper对象
        try {
            ret = db1.pst.executeQuery();
            while (ret.next()) {
                HfTaobaoShop hfTaobaoShop = new HfTaobaoShop();
                hfTaobaoShop.setShopId(ret.getInt(1));
                hfTaobaoShop.setShopDomain(ret.getString(2));
                // hfTaobaoShop.setShopName(ret.getString(3));
                listLink.add(hfTaobaoShop);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db1.close();
        }
        return listLink;
    }


    public void saveTaobaoLink(int shopId, String shopDomain, String title, String linkUrl) {
        StringBuffer sql = new StringBuffer("insert into hf_taobao_link(shop_id,shop_domain,title,taobao_good_link) values(");
        DBHelper db1 = null;
        ResultSet ret = null;
        //SQL语句
        sql.append(shopId);
        sql.append(",");
        sql.append("'");
        sql.append(shopDomain);
        sql.append("',");
        sql.append("'");
        sql.append(title);
        sql.append("',");

        sql.append("'");
        sql.append(linkUrl);
        sql.append("'");
        sql.append(")");

        db1 = new DBHelper(sql.toString());//创建DBHelper对象
        try {
         /* *//* db1.pst.setLong(1,hfGoods.getHfUid());
           db1.pst.setString(2,hfGoods.getShopName());*//*
           db1.pst.setString(1,hfGoods.getTitle());
           db1.pst.setInt(2,hfGoods.getWholesalePrice());
           db1.pst.setInt(3,hfGoods.getTaobaoPrice());
  *//*         db1.pst.setLong(6,hfGoods.getProductNo());
           db1.pst.setString(7,hfGoods.getSmallPicture());
           db1.pst.setString(8,hfGoods.getBigPicture());
           db1.pst.setInt(9,hfGoods.getInventory());*//*
           db1.pst.setString(4,hfGoods.getTaobaoLink());*/
            db1.pst.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db1.close();
        }
    }


    public List<HfTaobaoLink> getGoodLinkUrl() {
        StringBuffer sql = new StringBuffer("select shop_domain,taobao_good_link,shop_id from hf_taobao_link");
        DBHelper db1 = null;
        ResultSet ret = null;
        List<HfTaobaoLink> listLink = new ArrayList<HfTaobaoLink>();
        db1 = new DBHelper(sql.toString());//创建DBHelper对象
        try {
            ret = db1.pst.executeQuery();
            while (ret.next()) {
                HfTaobaoLink hfTaobaoLink = new HfTaobaoLink();
                hfTaobaoLink.setShopDomain(ret.getString(1));
                hfTaobaoLink.setTaobaoGoodLink(ret.getString(2));
                hfTaobaoLink.setShopId(ret.getInt(3));
                listLink.add(hfTaobaoLink);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db1.close();
        }
        return listLink;
    }


    public int getHfGoodsHfUid(long taobaoGoodId) {
        StringBuffer sql = new StringBuffer("select hf_uid from hf_goods where taobao_good_id = " + taobaoGoodId);
        DBHelper db1 = null;
        ResultSet ret = null;
        List<String> listLink = new ArrayList<String>();
        db1 = new DBHelper(sql.toString());//创建DBHelper对象
        int result = 0;
        try {
            ret = db1.pst.executeQuery();
            while (ret.next()) {
                result = ret.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db1.close();
        }
        return result;
    }

}
